﻿/*********************************************************************************************************************
VanguardDocumentation, Procedure SplitEntityType

© 2014 Erik Ackerholm

This file is part of SqlVanguard.

SqlVanguard is free software: you can redistribute it and/or modify it under the terms of the GNU General Public 
License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later 
version.

SqlVanguard is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied 
warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with SqlVanguard. If not, see 
<http://www.gnu.org/licenses/>.
*********************************************************************************************************************/

if
	not exists (
		select *
		from sys.schemas as s
			inner join sys.objects as o on (s.schema_id = o.schema_id)
		where s.name = 'VanguardDocumentation' and o.[type] = 'P'
			and o.name = 'SplitEntityType'
	)
begin
	declare @SqlCommand as nvarchar(max);
	set @SqlCommand = '
		create procedure VanguardDocumentation.SplitEntityType
		as raiserror (''Procedure SplitEntityType is not implemented yet!'', 11, 1);
	';
	execute (@SqlCommand);
end;
GO

alter procedure VanguardDocumentation.SplitEntityType
	/*****************************************************************************************************************
	Splits a dot-separated entity type into zero to three parts.

	If there are more than one entity with a given name, the user may choose to also give the type of the entity she 
	wishes to view or modify (if not the object will be choosen as the first according to SearchOrder in views 
	LevelXEntities). Like the name, the type is given as a dot-separated name and this procedure splits that name into 
	one to three parts (or zero parts if empty).

	The method for splitting the type is quite different than what we did with the name. Here the parts are matched 
	from the right. The @TypeLevel parameter tells us if the right-most part is at level 0, 1, or 2 and then parts to 
	the left (if any) follow at level 1 and/or 0.

	This is a bit complicated, so it's probably best to give examples.
	@FullEntityType                  @TypeLevel       @Level0Type    @Level1Type   @Level2Type
	Zero                             0                Zero                             
	One                              1                               One               
	Two                              2                                             Two
	Zero.One                         1                Zero           One           
	One.Two                          2                               One           Two
	Zero.One.Two                     2                Zero           One           Two

	Of course, if you would really pass 'One' as a type, the procedure would throw an error, because this is not an
	actual type. Also, if there are more parts in the name than what "fits" under the given @TypeLevel then an error 
	is thrown as well.
	*****************************************************************************************************************/
	
	@FullEntityType                varchar(128),
		/* Entity type that is to be split. */
	@TypeLevel                     tinyint,
		/* Level of the right-most part in the name. Split the name first and then input the level found for that 
		here. */
	@Level0Type                    varchar(64)                    output,
		/* First part of name. Is null for empty input, meaning properties are get/set at the database level. */
	@Level1Type                    varchar(64)                    output,
		/* Second part of name. Is null for one-part names (schemas and suchlike). */
	@Level2Type                    varchar(64)                    output
		/* Third part of name. Is null for one-part or two-part names (schemas, tables and suchlike). */
	
	with execute as owner
as
begin
	
	set nocount on;
	set xact_abort on;
	set lock_timeout 3000;
	set transaction isolation level read committed;
	
	declare @ErrorNo as integer;
	declare @ErrorMsg as nvarchar(1536);
	declare @ErrorLine as integer;
	declare @ErrorState as integer;
	
	begin try;
		
		if
			@TypeLevel not between 0 and 2 or @TypeLevel is null
		begin
			raiserror (740904, 16, 1, @TypeLevel);
			return;
		end;
		
		-- Empty type means database level. Returns at once for this to be able to catch errors in parsename,
		-- which will cause null output (same as with null or empty input).
		if
			@FullEntityType is null or @FullEntityType = ''
		begin
			return;
		end;
		
		-- Throw error if there are more parts than fit inside this level (as given by @TypeLevel)
		if
			parsename(@FullEntityType, (@TypeLevel + 2)) is not null
		begin
			raiserror (740905, 16, 2, @TypeLevel, @FullEntityType);
			return;
		end;
		
		-- Split name in 1, 2, or 3 parts
		if
			@TypeLevel = 0
		begin
			set @Level0Type = parsename(@FullEntityType, 1);
		end
		else if
			@TypeLevel = 1
		begin
			set @Level0Type = parsename(@FullEntityType, 2);
			set @Level1Type = parsename(@FullEntityType, 1);
		end
		else
		begin
			set @Level0Type = parsename(@FullEntityType, 3);
			set @Level1Type = parsename(@FullEntityType, 2);
			set @Level2Type = parsename(@FullEntityType, 1);
		end;
		
		-- If no parts were found, this means there was an error in parsename (returns null in that case).
		if
			@Level0Type is null and @Level1Type is null and @Level2Type is null
		begin
			raiserror (740903, 16, 3, @FullEntityType)
			return;
		end;
		
		-- Check that found parts are also known types.
		if
			@Level0Type not in (
				'SCHEMA', 'USER', 'ASSEMBLY', 'TRIGGER', 'FILEGROUP', 'PARTITION FUNCTION', 'PARTITION SCHEME', 
				'MESSAGE TYPE', 'CONTRACT, SERVICE', 'REMOTE SERVICE BINDING', 'ROUTE', 'EVENT NOTIFICATION'
			)
		begin
			raiserror (740906, 16, 4, @Level0Type, 0);
			return;
		end;
		
		if
			@Level1Type not in (
				'TABLE', 'VIEW', 'FUNCTION', 'PROCEDURE', 'TYPE', 'XML SCHEMA COLLECTION', 'SYNONYM', 'AGGREGATE',
				'QUEUE', 'LOGICAL FILE NAME'
			)
		begin
			raiserror (740906, 16, 5, @Level1Type, 1);
			return;
		end;
		
		if
			@Level2Type not in (
				'PARAMETER', 'COLUMN', 'CONSTRAINT', 'TRIGGER', 'INDEX', 'EVENT NOTIFICATION'
			)
		begin
			raiserror (740906, 16, 5, @Level2Type, 2);
			return;
		end;
		
	end try
	begin catch;
		
		set @ErrorNo = error_number();
		set @ErrorMsg = left(error_message(), 1536);
		set @ErrorLine = error_line();
		set @ErrorState = error_state();
		
		raiserror (740900, 16, @ErrorState, 'SplitEntityType', @ErrorNo, @ErrorLine, @ErrorMsg);
		
	end catch;
	
end;
GO




